{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import os\n",
    "import tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ad_static_feature.out', '.ipynb_checkpoints', 'ad_static.ipynb']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir('.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "106452\t1529958950\t22226\t16088\t13\t225\t\r\n",
      "233649\t1538221936\t25681\t7356\t13\t136\t1\r\n",
      "547531\t1550731020\t20696\t-1\t1\t186\t40\r\n",
      "707841\t1551857857\t3968\t-1\t3\t186\t40\r\n",
      "457009\t1550439402\t23614\t7447\t13\t172\t\r\n",
      "733436\t1552977426\t22405\t31722\t5\t117\t64\r\n",
      "249105\t1552641796\t11360\t29999\t18\t145\t44\r\n",
      "160014\t1552532512\t6441\t2373\t18\t198\t36\r\n",
      "541096\t1552467888\t5117\t220\t5\t232\t44\r\n",
      "634000\t1552292527\t28588\t20164\t18\t76\t64\r\n"
     ]
    }
   ],
   "source": [
    "!head ad_static_feature.out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature = pd.read_csv('ad_static_feature.out', sep='\\t', \n",
    "                                header=None, low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature.columns = ['ad_id', 'create-time', 'accunt-id', 'item-id', 'item-type',\n",
    "                            'trade-id', 'size']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ad_id</th>\n",
       "      <th>create-time</th>\n",
       "      <th>accunt-id</th>\n",
       "      <th>item-id</th>\n",
       "      <th>item-type</th>\n",
       "      <th>trade-id</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>106452</td>\n",
       "      <td>1529958950</td>\n",
       "      <td>22226</td>\n",
       "      <td>16088</td>\n",
       "      <td>13</td>\n",
       "      <td>225</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>233649</td>\n",
       "      <td>1538221936</td>\n",
       "      <td>25681</td>\n",
       "      <td>7356</td>\n",
       "      <td>13</td>\n",
       "      <td>136</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>547531</td>\n",
       "      <td>1550731020</td>\n",
       "      <td>20696</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>186</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>707841</td>\n",
       "      <td>1551857857</td>\n",
       "      <td>3968</td>\n",
       "      <td>-1</td>\n",
       "      <td>3</td>\n",
       "      <td>186</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>457009</td>\n",
       "      <td>1550439402</td>\n",
       "      <td>23614</td>\n",
       "      <td>7447</td>\n",
       "      <td>13</td>\n",
       "      <td>172</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ad_id  create-time  accunt-id item-id  item-type trade-id size\n",
       "0  106452   1529958950      22226   16088         13      225  NaN\n",
       "1  233649   1538221936      25681    7356         13      136    1\n",
       "2  547531   1550731020      20696      -1          1      186   40\n",
       "3  707841   1551857857       3968      -1          3      186   40\n",
       "4  457009   1550439402      23614    7447         13      172  NaN"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_static_feature.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 735911 entries, 0 to 735910\n",
      "Data columns (total 7 columns):\n",
      "ad_id          735911 non-null int64\n",
      "create-time    735911 non-null int64\n",
      "accunt-id      735911 non-null int64\n",
      "item-id        735911 non-null object\n",
      "item-type      735911 non-null int64\n",
      "trade-id       735911 non-null object\n",
      "size           509252 non-null object\n",
      "dtypes: int64(4), object(3)\n",
      "memory usage: 39.3+ MB\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-1             243527\n",
       "6199,28123          2\n",
       "19196,26277         1\n",
       "Name: item-id, dtype: int64"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_static_feature['item-id'].map(\n",
    "    lambda r : r if re.search('[^0-9]', r) != None else None).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ad_static_feature['trade-id'].map(\n",
    "#    lambda r : r if re.search(r'[^0-9]', r) != None else None).unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17,57    1\n",
       "Name: size, dtype: int64"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_static_feature['size'].dropna().map(\n",
    "    lambda r : r if re.search(r'[^0-9]', r) != None else None).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 735911 entries, 0 to 735910\n",
      "Data columns (total 7 columns):\n",
      "ad_id          735911 non-null int64\n",
      "create-time    735911 non-null int64\n",
      "accunt-id      735911 non-null int64\n",
      "item-id        735911 non-null object\n",
      "item-type      735911 non-null int64\n",
      "trade-id       735911 non-null object\n",
      "size           509252 non-null object\n",
      "dtypes: int64(4), object(3)\n",
      "memory usage: 39.3+ MB\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_ground = ad_static_feature[ad_static_feature['size'].isnull()].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 226659 entries, 0 to 735909\n",
      "Data columns (total 7 columns):\n",
      "ad_id          226659 non-null int64\n",
      "create-time    226659 non-null int64\n",
      "accunt-id      226659 non-null int64\n",
      "item-id        226659 non-null object\n",
      "item-type      226659 non-null int64\n",
      "trade-id       226659 non-null object\n",
      "size           0 non-null object\n",
      "dtypes: int64(4), object(3)\n",
      "memory usage: 13.8+ MB\n"
     ]
    }
   ],
   "source": [
    "ad_ground.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_ground[['item-type', 'trade-id', 'size']] = ad_ground[['item-id', 'item-type', 'trade-id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ad_id</th>\n",
       "      <th>create-time</th>\n",
       "      <th>accunt-id</th>\n",
       "      <th>item-id</th>\n",
       "      <th>item-type</th>\n",
       "      <th>trade-id</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>106452</td>\n",
       "      <td>1529958950</td>\n",
       "      <td>22226</td>\n",
       "      <td>16088</td>\n",
       "      <td>16088</td>\n",
       "      <td>13</td>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>457009</td>\n",
       "      <td>1550439402</td>\n",
       "      <td>23614</td>\n",
       "      <td>7447</td>\n",
       "      <td>7447</td>\n",
       "      <td>13</td>\n",
       "      <td>172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>111812</td>\n",
       "      <td>1553073106</td>\n",
       "      <td>2111</td>\n",
       "      <td>149</td>\n",
       "      <td>149</td>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>476327</td>\n",
       "      <td>1552699858</td>\n",
       "      <td>25436</td>\n",
       "      <td>8809</td>\n",
       "      <td>8809</td>\n",
       "      <td>8</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>257514</td>\n",
       "      <td>1552881181</td>\n",
       "      <td>5218</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ad_id  create-time  accunt-id item-id item-type  trade-id size\n",
       "0   106452   1529958950      22226   16088     16088        13  225\n",
       "4   457009   1550439402      23614    7447      7447        13  172\n",
       "15  111812   1553073106       2111     149       149         8   32\n",
       "19  476327   1552699858      25436    8809      8809         8  197\n",
       "21  257514   1552881181       5218      -1        -1         2   14"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_ground.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_ground['item-id'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_clean = ad_static_feature.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_clean.loc[ad_ground.index] = ad_ground"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 735911 entries, 0 to 735910\n",
      "Data columns (total 7 columns):\n",
      "ad_id          735911 non-null int64\n",
      "create-time    735911 non-null int64\n",
      "accunt-id      735911 non-null int64\n",
      "item-id        735911 non-null object\n",
      "item-type      735911 non-null object\n",
      "trade-id       735911 non-null object\n",
      "size           735911 non-null object\n",
      "dtypes: int64(3), object(4)\n",
      "memory usage: 39.3+ MB\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "230021     6199,28123\n",
       "273055     6199,28123\n",
       "302725    19196,26277\n",
       "Name: item-id, dtype: object"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_static_feature_clean['item-id'].map(lambda r : r if ',' in str(r) else None).dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Series([], Name: item-type, dtype: object)"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_static_feature_clean['item-type'].map(lambda r : r if ',' in str(r) else None).dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "59        238,198\n",
       "75        198,232\n",
       "77        238,198\n",
       "121        94,117\n",
       "130         99,24\n",
       "201       147,197\n",
       "221        117,93\n",
       "338         79,76\n",
       "520       122,155\n",
       "608        32,155\n",
       "655        13,117\n",
       "670        94,117\n",
       "768        118,25\n",
       "811        147,54\n",
       "908        117,93\n",
       "914         3,117\n",
       "974        91,197\n",
       "1008      249,111\n",
       "1101      212,170\n",
       "1106       144,94\n",
       "1348       211,50\n",
       "1350      212,170\n",
       "1441      144,117\n",
       "1522       154,24\n",
       "1571      144,117\n",
       "1602       13,117\n",
       "1715      172,145\n",
       "1915      147,197\n",
       "1986       147,54\n",
       "2063      160,230\n",
       "           ...   \n",
       "725500      91,32\n",
       "726637     94,117\n",
       "726767     32,147\n",
       "727275     13,117\n",
       "727699    232,117\n",
       "727914    186,150\n",
       "728208     186,76\n",
       "728681    117,218\n",
       "729203     57,222\n",
       "730268     60,197\n",
       "730275    127,117\n",
       "730405     186,76\n",
       "730490     13,117\n",
       "730645    238,198\n",
       "731062     94,117\n",
       "731114    117,218\n",
       "731571     113,60\n",
       "731676     94,117\n",
       "731953    238,198\n",
       "732198     206,93\n",
       "732477    107,186\n",
       "732530     91,147\n",
       "733349     186,76\n",
       "733606      91,54\n",
       "733920    127,117\n",
       "734484    186,147\n",
       "734674    147,197\n",
       "735169     13,117\n",
       "735512     91,197\n",
       "735601     94,117\n",
       "Name: trade-id, Length: 7096, dtype: object"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_static_feature_clean['trade-id'].map(lambda r : r if ',' in str(r) else None).dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 735911 entries, 0 to 735910\n",
      "Data columns (total 7 columns):\n",
      "ad_id          735911 non-null int64\n",
      "create-time    735911 non-null int64\n",
      "accunt-id      735911 non-null int64\n",
      "item-id        735911 non-null object\n",
      "item-type      735911 non-null object\n",
      "trade-id       735911 non-null object\n",
      "size           735911 non-null object\n",
      "dtypes: int64(3), object(4)\n",
      "memory usage: 39.3+ MB\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_clean['item-id'] = ad_static_feature_clean['item-id'].map(\n",
    "    lambda r : None if ',' in str(r) else r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_clean['size'] = ad_static_feature_clean['size'].map(\n",
    "    lambda r : None if ',' in str(r) else r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_clean = ad_static_feature_clean.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "_ = ad_static_feature_clean['item-id'].astype('int64', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/lib/python3/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_clean['item-type'] = ad_static_feature_clean['item-type'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/lib/python3/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_clean['size'] = ad_static_feature_clean['size'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 734812 entries, 0 to 735910\n",
      "Data columns (total 7 columns):\n",
      "ad_id          734812 non-null int64\n",
      "create-time    734812 non-null int64\n",
      "accunt-id      734812 non-null int64\n",
      "item-id        734812 non-null int64\n",
      "item-type      734812 non-null int64\n",
      "trade-id       734812 non-null object\n",
      "size           734812 non-null int64\n",
      "dtypes: int64(6), object(1)\n",
      "memory usage: 44.8+ MB\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi = ad_static_feature_clean.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi['trade-id'] = ad_multi['trade-id'].map(lambda r : r if ',' in str(r) else None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi = ad_multi.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi['trade-id1'] = ad_multi['trade-id'].map(lambda r : int(r.split(',')[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi['trade-id2'] = ad_multi['trade-id'].map(lambda r : int(r.split(',')[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ad_id</th>\n",
       "      <th>create-time</th>\n",
       "      <th>accunt-id</th>\n",
       "      <th>item-id</th>\n",
       "      <th>item-type</th>\n",
       "      <th>trade-id</th>\n",
       "      <th>size</th>\n",
       "      <th>trade-id1</th>\n",
       "      <th>trade-id2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>602413</td>\n",
       "      <td>1552639144</td>\n",
       "      <td>12560</td>\n",
       "      <td>21425</td>\n",
       "      <td>18</td>\n",
       "      <td>238,198</td>\n",
       "      <td>64</td>\n",
       "      <td>238</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>577301</td>\n",
       "      <td>1552459593</td>\n",
       "      <td>26347</td>\n",
       "      <td>21425</td>\n",
       "      <td>18</td>\n",
       "      <td>198,232</td>\n",
       "      <td>64</td>\n",
       "      <td>198</td>\n",
       "      <td>232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>594605</td>\n",
       "      <td>1551840342</td>\n",
       "      <td>5090</td>\n",
       "      <td>22641</td>\n",
       "      <td>5</td>\n",
       "      <td>238,198</td>\n",
       "      <td>36</td>\n",
       "      <td>238</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>102412</td>\n",
       "      <td>1551252909</td>\n",
       "      <td>26217</td>\n",
       "      <td>424</td>\n",
       "      <td>5</td>\n",
       "      <td>94,117</td>\n",
       "      <td>64</td>\n",
       "      <td>94</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>585421</td>\n",
       "      <td>1552718647</td>\n",
       "      <td>5625</td>\n",
       "      <td>20657</td>\n",
       "      <td>8</td>\n",
       "      <td>99,24</td>\n",
       "      <td>40</td>\n",
       "      <td>99</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      ad_id  create-time  accunt-id  item-id  item-type trade-id  size  \\\n",
       "59   602413   1552639144      12560    21425         18  238,198    64   \n",
       "75   577301   1552459593      26347    21425         18  198,232    64   \n",
       "77   594605   1551840342       5090    22641          5  238,198    36   \n",
       "121  102412   1551252909      26217      424          5   94,117    64   \n",
       "130  585421   1552718647       5625    20657          8    99,24    40   \n",
       "\n",
       "     trade-id1  trade-id2  \n",
       "59         238        198  \n",
       "75         198        232  \n",
       "77         238        198  \n",
       "121         94        117  \n",
       "130         99         24  "
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_multi.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi = ad_multi.drop('trade-id', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi1 = ad_multi.copy()\n",
    "ad_multi2 = ad_multi.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi1['trade-id'] = ad_multi1['trade-id1']\n",
    "ad_multi2['trade-id'] = ad_multi2['trade-id2']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi1 = ad_multi1.drop(['trade-id1', 'trade-id2'], axis=1)\n",
    "ad_multi2 = ad_multi2.drop(['trade-id1', 'trade-id2'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_multi = pd.concat([ad_multi1, ad_multi2], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 734812 entries, 0 to 735910\n",
      "Data columns (total 7 columns):\n",
      "ad_id          734812 non-null int64\n",
      "create-time    734812 non-null int64\n",
      "accunt-id      734812 non-null int64\n",
      "item-id        734812 non-null int64\n",
      "item-type      734812 non-null int64\n",
      "trade-id       734812 non-null object\n",
      "size           734812 non-null int64\n",
      "dtypes: int64(6), object(1)\n",
      "memory usage: 44.8+ MB\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_clean['trade-id'] = ad_static_feature_clean['trade-id'].map(\n",
    "                        lambda r : None if ',' in str(r) else r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_clean = ad_static_feature_clean.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 727716 entries, 0 to 735910\n",
      "Data columns (total 7 columns):\n",
      "ad_id          727716 non-null int64\n",
      "create-time    727716 non-null int64\n",
      "accunt-id      727716 non-null int64\n",
      "item-id        727716 non-null int64\n",
      "item-type      727716 non-null int64\n",
      "trade-id       727716 non-null object\n",
      "size           727716 non-null int64\n",
      "dtypes: int64(6), object(1)\n",
      "memory usage: 44.4+ MB\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_clean.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((727716, 7), (14192, 7))"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_static_feature_clean.shape, ad_multi.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/lib/python3/dist-packages/ipykernel_launcher.py:1: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_all = pd.concat([ad_static_feature_clean, ad_multi], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_all['trade-id'] = ad_static_feature_all['trade-id'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 741908 entries, 0 to 735601\n",
      "Data columns (total 7 columns):\n",
      "accunt-id      741908 non-null int64\n",
      "ad_id          741908 non-null int64\n",
      "create-time    741908 non-null int64\n",
      "item-id        741908 non-null int64\n",
      "item-type      741908 non-null int64\n",
      "size           741908 non-null int64\n",
      "trade-id       741908 non-null int64\n",
      "dtypes: int64(7)\n",
      "memory usage: 45.3 MB\n"
     ]
    }
   ],
   "source": [
    "ad_static_feature_all.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>accunt-id</th>\n",
       "      <th>ad_id</th>\n",
       "      <th>create-time</th>\n",
       "      <th>item-id</th>\n",
       "      <th>item-type</th>\n",
       "      <th>size</th>\n",
       "      <th>trade-id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22226</td>\n",
       "      <td>106452</td>\n",
       "      <td>1529958950</td>\n",
       "      <td>0</td>\n",
       "      <td>16088</td>\n",
       "      <td>225</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25681</td>\n",
       "      <td>233649</td>\n",
       "      <td>1538221936</td>\n",
       "      <td>7356</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20696</td>\n",
       "      <td>547531</td>\n",
       "      <td>1550731020</td>\n",
       "      <td>-1</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3968</td>\n",
       "      <td>707841</td>\n",
       "      <td>1551857857</td>\n",
       "      <td>-1</td>\n",
       "      <td>3</td>\n",
       "      <td>40</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23614</td>\n",
       "      <td>457009</td>\n",
       "      <td>1550439402</td>\n",
       "      <td>0</td>\n",
       "      <td>7447</td>\n",
       "      <td>172</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   accunt-id   ad_id  create-time  item-id  item-type  size  trade-id\n",
       "0      22226  106452   1529958950        0      16088   225        13\n",
       "1      25681  233649   1538221936     7356         13     1       136\n",
       "2      20696  547531   1550731020       -1          1    40       186\n",
       "3       3968  707841   1551857857       -1          3    40       186\n",
       "4      23614  457009   1550439402        0       7447   172        13"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ad_static_feature_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "ad_static_feature_all.to_hdf('ad_static_feature.h5', key='ad_static', mode='w')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_hdf('ad_static_feature.h5', 'ad_static')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(741908, 7)"
      ]
     },
     "execution_count": 197,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(516344, 7)"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['item-id'] != 0].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
